import pandas as pd
import os
from pandas import read_csv
from datetime import datetime

# 定义输入和输出文件夹路径
input_folder = "/root/autodl-tmp/bdata/two"
output_file = "result_2.xlsx"

# 获取输入文件夹中所有以 P 开头且以 .csv 结尾的文件
file_list = [f for f in os.listdir(input_folder) if f.startswith('P') and f.endswith('.csv')]
all_results = []
def time_count(filtered_df):
    result = (filtered_df.shape[0]-1)*10
    result = round(result/(1000*60*60),4)
    return result
def df_pred(file_path):
    # 修改为使用自定义解析函数读取 CSV 文件
    df = read_csv(file_path, header=0, parse_dates=[0])
    # 将 class 和 MET 列转换为数值类型
    df['class'] = pd.to_numeric(df['class'])
    df['MET'] = pd.to_numeric(df['MET'])
    # class 列保留整数部分
    df['class'] = df['class'].round(1)
    # MET 列保留两位小数
    df['MET'] = df['MET'].round(2)
    return df
for file_name in file_list:
    file_path = os.path.join(input_folder, file_name)
    try:
        df =  df_pred(file_path)
        df = df[df.iloc[:, 4].notna() & df.iloc[:, 5].notna()]

        # 将第六列（索引为 5）转换为 float 类型
        df.iloc[:, 5] = pd.to_numeric(df.iloc[:, 5], errors='coerce')
        # 筛选出第六列值小于 1 的行
        filtered_df = df[df.iloc[:, 5] < 1.0]
        result_sleep = time_count(filtered_df)
        # 筛选met在1~1.6的
        filtered_df = df[(df.iloc[:, 5] >=1) & (df.iloc[:, 5] < 1.6)]
        result_static = time_count(filtered_df)
        # 筛选met在1.6~3.0的
        filtered_df = df[(df.iloc[:, 5] >=1.6) & (df.iloc[:, 5] < 3.0)]
        result_low = time_count(filtered_df)
        # 筛选met在3.0~6.0的
        filtered_df = df[(df.iloc[:, 5] >=3.0) & (df.iloc[:, 5] < 6.0)]
        result_moderate = time_count(filtered_df)
        # 筛选met大于6.0的
        filtered_df = df[df.iloc[:, 5] >=6.0]
        result_intensive = time_count(filtered_df)
        
        result_all = result_sleep + result_static + result_low +  result_moderate + result_intensive
        results = [file_name, result_all, result_sleep, result_intensive, result_moderate, result_low, result_static]
        all_results.append(results)

    except Exception as e:
        print(f"Error processing {file_path}: {e}")
        
columns = ['志愿者ID', '记录总时长(小时)', '睡眠总时长(小时)', '高强度运动总时长(小时)', '中强度运动总时长(小时)', '低强度运动总时长(小时)', '静态运动总时长(小时)']
result_df = pd.DataFrame(all_results, columns=columns)
try:
    result_df.to_excel(output_file, index=False)
    print(f"文件已成功保存到 {output_file}")
except PermissionError:
    print(f"权限不足，无法保存文件 {output_file}。请确保文件未被其他程序占用，并且你有写入权限。")